﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetXMissionStructurePole_CreateByInspVisitXml')
	DROP PROCEDURE up_UtilityAssetXMissionStructurePole_CreateByInspVisitXml
GO


CREATE PROCEDURE [dbo].[up_UtilityAssetXMissionStructurePole_CreateByInspVisitXml]
(
	@vendorProjectId int,
	@xml			xml
) 
AS

DECLARE @UtilityId int
DECLARE @UtilityProjectId int

--Gets the Utility ID
SELECT 
	@UtilityId = UtilityFk
FROM 
	tb_vendor_Projects
WHERE  
	Vendor_ProjectId = @vendorProjectId

--Gets the Utility Project Id
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId

PRINT 'Prepare table used to update/insert assets'

declare @assetsToCreate as table (XMissionInspectionVisitId int, XMissionAssetId int)

INSERT INTO @assetsToCreate
(
	XMissionInspectionVisitId,
	XMissionAssetId
)
SELECT 
	Item.query('.').value('(VisitToAsset/Visit)[1]', 'int') as VisitId,
	CASE 
		WHEN Item.query('.').value('(VisitToAsset/Asset)[1]', 'int') = 0 THEN NULL
		ELSE Item.query('.').value('(VisitToAsset/Asset)[1]', 'int')
	END as AssetId
FROM
	@xml.nodes('/VisitsToAssets/VisitToAsset') T(Item) 

PRINT 'Update Asset Fk from XMission inspection visit'

UPDATE 
	tb_Vendor_InspVisit_XmissionStructures_Poles
SET
	Utility_Asset_XMissionStructures_PoleFk = XMissionAssetId
FROM 
	@assetsToCreate
WHERE
	Vendor_InspVisit_XMissionStructure_PoleId = XMissionInspectionVisitId

UPDATE 
	tb_Vendor_InspVisit_XmissionStructures
SET
	Utility_Asset_XMissionStructureFK = assets.Utility_Asset_XMissionStructureFK
FROM
	@assetsToCreate assetsToCreate
INNER JOIN tb_Utility_Asset_XMissionStructures_Poles assets
	ON assets.Utility_Asset_XMissionStructures_PoleId = assetsToCreate.XMissionAssetId


PRINT 'Add Assets from visits'
DECLARE @insertedStructureAssetIds TABLE (Utility_Asset_StructureId int, TransmissionPoleId int)

INSERT INTO tb_Utility_Asset_XMissionStructures
(
	UtilityFK
	,Latitude
	,Longitude
	,Altitude
	,SolutionAvailable
	,XMissionStructures_TopTypeFk
	,Utility_ContactFK
	,Date
	,Utility_ProjectFK
	,HaveImages
)
OUTPUT INSERTED.Utility_Asset_XMissionStructureId, INSERTED.Altitude INTO @insertedStructureAssetIds
SELECT 
	@UtilityId
	,Latitude
	,Longitude
	,Vendor_InspVisit_XMissionStructure_PoleId
	,1
	,TopTypeFk
	,inspectorfk
	,GETDATE()
	,@UtilityProjectId
	,0
FROM 
	tb_Vendor_InspVisit_XmissionStructures_Poles
WHERE 
	vendor_projectfk = @vendorProjectId
	AND Utility_Asset_XMissionStructures_PoleFk IS NULL
	AND tb_Vendor_InspVisit_XmissionStructures_Poles.Vendor_InspVisit_XMissionStructure_PoleId in (SELECT XMissionInspectionVisitId FROM @assetsToCreate)

DECLARE @insertedAssetIds TABLE (Utility_Asset_TransmissionId int, TransmissionPoleId int)

INSERT INTO tb_Utility_Asset_XMissionStructures_Poles
(
	Utility_Asset_XMissionStructureFK,
	XMissionStructures_PoleTopTypeFk,
	XMissionStructures_PoleSpeciesFk,
	XMissionStructures_PoleTreatmentFk,
	[Year],
	XMissionStructures_PoleClassFk,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	AID,
	LVisit,
	Number,
	Length,
	LVisitor,
	MapFk,
	SectionFk
)
OUTPUT INSERTED.Utility_Asset_XMissionStructures_PoleId, INSERTED.Altitude INTO @insertedAssetIds
SELECT 
	[AssetStructures].Utility_Asset_StructureId,
	TopTypeFk,
	SpecieFk,
	TreatmentFk,
	[Year],
	ClassFk,
	Latitude,
	Longitude,
	Vendor_InspVisit_XMissionStructure_PoleId,
	NULL,
	NULL,
	1,
	NULL,
	YEAR(LVisit),
	Number,
	Length,
	LInspector,
	MapFk,
	SectionFk	
FROM 
	tb_Vendor_InspVisit_XmissionStructures_Poles inspPoles
INNER JOIN @insertedStructureAssetIds [AssetStructures]
	ON [AssetStructures].TransmissionPoleId = inspPoles.Vendor_InspVisit_XMissionStructure_PoleId
WHERE 
	vendor_projectfk = @vendorProjectId
	AND Utility_Asset_XMissionStructures_PoleFk IS NULL
	AND inspPoles.Vendor_InspVisit_XMissionStructure_PoleId in (SELECT XMissionInspectionVisitId FROM @assetsToCreate)


UPDATE
	tb_Vendor_InspVisit_XmissionStructures_Poles
SET
	Utility_Asset_XMissionStructures_PoleFk = [InsertedIds].Utility_Asset_TransmissionId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_InspVisit_XmissionStructures_Poles.Vendor_InspVisit_XMissionStructure_PoleId = [InsertedIds].TransmissionPoleId

UPDATE 
	tb_Utility_Asset_XMissionStructures_Poles
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_XMissionStructures_Poles
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_XMissionStructures_Poles.Utility_Asset_XMissionStructures_PoleId = [InsertedIds].Utility_Asset_TransmissionId

UPDATE 
	tb_Utility_Asset_XMissionStructures
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_XMissionStructures
INNER JOIN @insertedStructureAssetIds [InsertedIds]
	ON [InsertedIds].Utility_Asset_StructureId = tb_Utility_Asset_XMissionStructures.Utility_Asset_XMissionStructureId

 